using SPI.AgentServer.Services.Execution;
using System.Text;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 人员出入详情查询工具 - 使用 TblPersonnelInOut 表
/// 覆盖场景：P-010 人员出入详情
/// </summary>
public class GetPersonnelInOutDetailTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ILogger<GetPersonnelInOutDetailTool> _logger;

    public string Name => "get_personnel_inout_detail";
    public string Description => "查询人员在特定位置的进出详情（区别于闸机记录）。用于回答'查看今天图书馆人员出入'、'某位置的人员进出情况'等问题。";

    public GetPersonnelInOutDetailTool(
        IDatabaseExecutor executor,
        ILogger<GetPersonnelInOutDetailTool> logger)
    {
        _executor = executor;
        _logger = logger;
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        return true;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (parameters == null)
        {
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { "参数不能为空" }
            };
        }

        try
        {
            string? location = parameters.ContainsKey("location") ? parameters["location"]?.ToString() : null;
            string? queryDate = parameters.ContainsKey("queryDate") ? parameters["queryDate"]?.ToString() : null;
            int? inOutInfo = parameters.ContainsKey("inOutInfo") ? Convert.ToInt32(parameters["inOutInfo"]) : null;

            if (string.IsNullOrEmpty(queryDate))
            {
                queryDate = DateTime.Today.ToString("yyyy-MM-dd");
            }

            var sql = BuildQuery(location, queryDate, inOutInfo);

            _logger.LogInformation("Executing personnel in-out detail query with SQL: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = "人员出入详情查询成功",
                Metadata = new Dictionary<string, object>
                {
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing personnel in-out detail query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询失败: {ex.Message}" }
            };
        }
    }

    private string BuildQuery(string? location, string queryDate, int? inOutInfo)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    Name AS name,");
        sb.AppendLine("    CASE InOutInfo");
        sb.AppendLine("        WHEN 1 THEN '进入'");
        sb.AppendLine("        WHEN 0 THEN '离开'");
        sb.AppendLine("        ELSE '未知'");
        sb.AppendLine("    END AS action,");
        sb.AppendLine("    Location AS location,");
        sb.AppendLine("    DATE_FORMAT(EnrollmentTime, '%Y-%m-%d %H:%i:%s') AS enrollment_time");
        sb.AppendLine("FROM TblPersonnelInOut");
        sb.AppendLine($"WHERE DATE(EnrollmentTime) = '{queryDate}'");

        if (!string.IsNullOrEmpty(location))
        {
            sb.AppendLine($"    AND Location LIKE '%{location}%'");
        }

        if (inOutInfo.HasValue)
        {
            sb.AppendLine($"    AND InOutInfo = {inOutInfo.Value}");
        }

        sb.AppendLine("ORDER BY EnrollmentTime DESC");
        sb.AppendLine("LIMIT 100");

        return sb.ToString();
    }
}
